"""
数据库迁移脚本
"""

import asyncio
from datetime import datetime
from ..core.database import get_db_session, engine
from ..core.logging import get_logger

logger = get_logger(__name__)


# 初始数据库架构
@migration("001_initial_schema", "Create initial database schema")
class InitialSchema:
    
    async def up(self):
        """创建初始表结构"""
        try:
            # 导入所有模型以确保表被创建
            from ..models import user, audiobook, voice_profile
            
            # 创建所有表
            async with engine.begin() as conn:
                await conn.run_sync(user.User.metadata.create_all)
                await conn.run_sync(audiobook.AudioBook.metadata.create_all)
                await conn.run_sync(voice_profile.VoiceProfile.metadata.create_all)
            
            logger.info("Initial schema created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create initial schema: {e}")
            raise
    
    async def down(self):
        """删除初始表结构"""
        try:
            from ..models import user, audiobook, voice_profile
            
            async with engine.begin() as conn:
                await conn.run_sync(user.User.metadata.drop_all)
                await conn.run_sync(audiobook.AudioBook.metadata.drop_all)
                await conn.run_sync(voice_profile.VoiceProfile.metadata.drop_all)
            
            logger.info("Initial schema dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop initial schema: {e}")
            raise


# 添加语音配置相关表
@migration("002_add_voice_profiles", "Add voice profile related tables")
class AddVoiceProfiles:
    
    async def up(self):
        """添加语音配置表"""
        try:
            from ..models.voice_profile import VoicePreset, VoiceProfileReview, VoiceFavorite
            
            async with engine.begin() as conn:
                await conn.run_sync(VoicePreset.metadata.create_all)
                await conn.run_sync(VoiceProfileReview.metadata.create_all)
                await conn.run_sync(VoiceFavorite.metadata.create_all)
            
            logger.info("Voice profile tables created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create voice profile tables: {e}")
            raise
    
    async def down(self):
        """删除语音配置表"""
        try:
            from ..models.voice_profile import VoicePreset, VoiceProfileReview, VoiceFavorite
            
            async with engine.begin() as conn:
                await conn.run_sync(VoicePreset.metadata.drop_all)
                await conn.run_sync(VoiceProfileReview.metadata.drop_all)
                await conn.run_sync(VoiceFavorite.metadata.drop_all)
            
            logger.info("Voice profile tables dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop voice profile tables: {e}")
            raise


# 添加音频流相关表
@migration("003_add_audio_streams", "Add audio stream related tables")
class AddAudioStreams:
    
    async def up(self):
        """添加音频流表"""
        try:
            from ..models.audio_stream import (
                AudioStream, StreamQualityEvent, StreamConnectionLog,
                StreamBuffer, StreamSession
            )
            
            async with engine.begin() as conn:
                await conn.run_sync(AudioStream.metadata.create_all)
                await conn.run_sync(StreamQualityEvent.metadata.create_all)
                await conn.run_sync(StreamConnectionLog.metadata.create_all)
                await conn.run_sync(StreamBuffer.metadata.create_all)
                await conn.run_sync(StreamSession.metadata.create_all)
            
            logger.info("Audio stream tables created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create audio stream tables: {e}")
            raise
    
    async def down(self):
        """删除音频流表"""
        try:
            from ..models.audio_stream import (
                AudioStream, StreamQualityEvent, StreamConnectionLog,
                StreamBuffer, StreamSession
            )
            
            async with engine.begin() as conn:
                await conn.run_sync(AudioStream.metadata.drop_all)
                await conn.run_sync(StreamQualityEvent.metadata.drop_all)
                await conn.run_sync(StreamConnectionLog.metadata.drop_all)
                await conn.run_sync(StreamBuffer.metadata.drop_all)
                await conn.run_sync(StreamSession.metadata.drop_all)
            
            logger.info("Audio stream tables dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop audio stream tables: {e}")
            raise


# 添加任务系统表
@migration("004_add_task_system", "Add task system tables")
class AddTaskSystem:
    
    async def up(self):
        """添加任务系统表"""
        try:
            from ..models.task import Task, TaskLog, TaskSchedule, TaskQueue, TaskDependency
            
            async with engine.begin() as conn:
                await conn.run_sync(Task.metadata.create_all)
                await conn.run_sync(TaskLog.metadata.create_all)
                await conn.run_sync(TaskSchedule.metadata.create_all)
                await conn.run_sync(TaskQueue.metadata.create_all)
                await conn.run_sync(TaskDependency.metadata.create_all)
            
            logger.info("Task system tables created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create task system tables: {e}")
            raise
    
    async def down(self):
        """删除任务系统表"""
        try:
            from ..models.task import Task, TaskLog, TaskSchedule, TaskQueue, TaskDependency
            
            async with engine.begin() as conn:
                await conn.run_sync(Task.metadata.drop_all)
                await conn.run_sync(TaskLog.metadata.drop_all)
                await conn.run_sync(TaskSchedule.metadata.drop_all)
                await conn.run_sync(TaskQueue.metadata.drop_all)
                await conn.run_sync(TaskDependency.metadata.drop_all)
            
            logger.info("Task system tables dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop task system tables: {e}")
            raise


# 添加用户设置表
@migration("005_add_user_settings", "Add user settings tables")
class AddUserSettings:
    
    async def up(self):
        """添加用户设置表"""
        try:
            from ..models.user import UserSession, UserAPIKey, UserActivity, UserSettings
            from ..models.voice_profile import VoiceUsageLog
            
            async with engine.begin() as conn:
                await conn.run_sync(UserSession.metadata.create_all)
                await conn.run_sync(UserAPIKey.metadata.create_all)
                await conn.run_sync(UserActivity.metadata.create_all)
                await conn.run_sync(UserSettings.metadata.create_all)
                await conn.run_sync(VoiceUsageLog.metadata.create_all)
            
            logger.info("User settings tables created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create user settings tables: {e}")
            raise
    
    async def down(self):
        """删除用户设置表"""
        try:
            from ..models.user import UserSession, UserAPIKey, UserActivity, UserSettings
            from ..models.voice_profile import VoiceUsageLog
            
            async with engine.begin() as conn:
                await conn.run_sync(UserSession.metadata.drop_all)
                await conn.run_sync(UserAPIKey.metadata.drop_all)
                await conn.run_sync(UserActivity.metadata.create_all)
                await conn.run_sync(UserSettings.metadata.create_all)
                await conn.run_sync(VoiceUsageLog.metadata.drop_all)
            
            logger.info("User settings tables dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop user settings tables: {e}")
            raise


# 添加分析表
@migration("006_add_analytics_tables", "Add analytics tables")
class AddAnalyticsTables:
    
    async def up(self):
        """添加分析表"""
        try:
            # 创建使用统计表
            async with get_db_session() as session:
                await session.execute("""
                    CREATE TABLE IF NOT EXISTS usage_statistics (
                        id VARCHAR(36) PRIMARY KEY,
                        date DATE NOT NULL,
                        user_id VARCHAR(36),
                        audiobook_id VARCHAR(36),
                        voice_profile_id VARCHAR(36),
                        total_requests INTEGER DEFAULT 0,
                        total_duration FLOAT DEFAULT 0.0,
                        avg_duration FLOAT DEFAULT 0.0,
                        success_rate FLOAT DEFAULT 0.0,
                        error_rate FLOAT DEFAULT 0.0,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_usage_date (date),
                        INDEX idx_usage_user_id (user_id),
                        INDEX idx_usage_audiobook_id (audiobook_id)
                    )
                """)
                
                # 创建系统监控表
                await session.execute("""
                    CREATE TABLE IF NOT EXISTS system_metrics (
                        id VARCHAR(36) PRIMARY KEY,
                        metric_name VARCHAR(100) NOT NULL,
                        metric_value FLOAT NOT NULL,
                        metric_unit VARCHAR(20),
                        tags JSON,
                        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_metric_name (metric_name),
                        INDEX idx_metric_timestamp (timestamp)
                    )
                """)
                
                # 创建错误日志表
                await session.execute("""
                    CREATE TABLE IF NOT EXISTS error_logs (
                        id VARCHAR(36) PRIMARY KEY,
                        error_type VARCHAR(50) NOT NULL,
                        error_message TEXT NOT NULL,
                        stack_trace TEXT,
                        user_id VARCHAR(36),
                        request_id VARCHAR(36),
                        context JSON,
                        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_error_type (error_type),
                        INDEX idx_error_timestamp (timestamp),
                        INDEX idx_error_user_id (user_id)
                    )
                """)
                
                await session.commit()
            
            logger.info("Analytics tables created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create analytics tables: {e}")
            raise
    
    async def down(self):
        """删除分析表"""
        try:
            async with get_db_session() as session:
                await session.execute("DROP TABLE IF EXISTS usage_statistics")
                await session.execute("DROP TABLE IF EXISTS system_metrics")
                await session.execute("DROP TABLE IF EXISTS error_logs")
                await session.commit()
            
            logger.info("Analytics tables dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop analytics tables: {e}")
            raise


# 添加缓存表
@migration("007_add_caching_tables", "Add caching tables")
class AddCachingTables:
    
    async def up(self):
        """添加缓存表"""
        try:
            async with get_db_session() as session:
                # 创建缓存表
                await session.execute("""
                    CREATE TABLE IF NOT EXISTS cache_entries (
                        key VARCHAR(255) PRIMARY KEY,
                        value MEDIUMBLOB NOT NULL,
                        expiration TIMESTAMP NOT NULL,
                        content_type VARCHAR(100),
                        size_bytes INTEGER DEFAULT 0,
                        access_count INTEGER DEFAULT 0,
                        last_access TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        INDEX idx_cache_expiration (expiration),
                        INDEX idx_cache_last_access (last_access)
                    )
                """)
                
                # 创建文件缓存表
                await session.execute("""
                    CREATE TABLE IF NOT EXISTS file_cache (
                        id VARCHAR(36) PRIMARY KEY,
                        file_path VARCHAR(500) NOT NULL,
                        file_hash VARCHAR(64) NOT NULL,
                        file_size INTEGER NOT NULL,
                        content_type VARCHAR(100),
                        audiobook_id VARCHAR(36),
                        user_id VARCHAR(36),
                        access_count INTEGER DEFAULT 0,
                        last_access TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        expires_at TIMESTAMP,
                        INDEX idx_file_hash (file_hash),
                        INDEX idx_file_audiobook_id (audiobook_id),
                        INDEX idx_file_expires_at (expires_at),
                        UNIQUE KEY unique_file_path (file_path)
                    )
                """)
                
                # 创建会话缓存表
                await session.execute("""
                    CREATE TABLE IF NOT EXISTS session_cache (
                        session_id VARCHAR(255) PRIMARY KEY,
                        user_id VARCHAR(36) NOT NULL,
                        data MEDIUMBLOB NOT NULL,
                        last_access TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        expires_at TIMESTAMP NOT NULL,
                        INDEX idx_session_user_id (user_id),
                        INDEX idx_session_expires_at (expires_at)
                    )
                """)
                
                await session.commit()
            
            logger.info("Caching tables created successfully")
            
        except Exception as e:
            logger.error(f"Failed to create caching tables: {e}")
            raise
    
    async def down(self):
        """删除缓存表"""
        try:
            async with get_db_session() as session:
                await session.execute("DROP TABLE IF EXISTS cache_entries")
                await session.execute("DROP TABLE IF EXISTS file_cache")
                await session.execute("DROP TABLE IF EXISTS session_cache")
                await session.commit()
            
            logger.info("Caching tables dropped successfully")
            
        except Exception as e:
            logger.error(f"Failed to drop caching tables: {e}")
            raise


# 导入装饰器
from .runner import migration
